﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jb.Common.Extensions;
using Jb.Domain;
using System.Data;

namespace Jb.Search.Searcher
{
    public class SavedSearcher 
    {
        private JbEntities _Context;
        private String _CategoryIDs = String.Empty;

        public SavedSearcher(JbEntities context, String categoryIDs = null)
        {
            if (context == null)
                throw new ArgumentException("Context must be defined");

            _Context = context;
            _CategoryIDs = categoryIDs;
        }

        public void SaveSearch(SavedSearch entity)
        {
            _Context.SaveChanges();
        }

        /// <summary>
        /// Gets a List of SavedSearches by JobSeekerID
        /// </summary>
        /// <param name="jobSeekerID"></param>
        /// <returns></returns>
        public List<SavedSearch> GetByJobSeeker(int jobSeekerID)
        {
            List<SavedSearch> searches = (from P in _Context.SavedSearches where P.JobSeekerID == jobSeekerID select P).ToList();
            return searches;
        }

        /// <summary>
        /// Executes a Saved Search and returns result as DataSet
        /// </summary>
        /// <param name="entity"></param>
        /// <param name="pageNo"></param>
        /// <param name="pageSize"></param>
        /// <param name="totalRecord"></param>
        /// <returns>Returns a DataSet of the jobs matching search criteria or null if there is no record found.</returns>
        public DataSet ExecuteSearch(SavedSearch entity, int pageNo, int pageSize, out int totalRecord)
        {
            ///Check whether the keyword matches any job index in Lucene.
            String luceneJobIDs = String.Empty;
            if (!entity.Keyword.IsNullOrEmpty())
            {
                ISearcher searcher = SearcherFactory.GetSearcher(typeof(Job));
                ///Following Luncene index search is similar to job search of App.Web.Controllers.JobController.Search method
                luceneJobIDs = searcher.Search(entity.Keyword);
                if (luceneJobIDs.IsNullOrEmpty())
                {
                    totalRecord = 0;
                    return null;
                }
            }
            StringBuilder SQL = new StringBuilder(10);
            GenerateSQL(entity, SQL);
            //Finally Add String Format info in the SQL for Pagination
            SQL.Replace("@StartRecord", "{0}");
            SQL.Replace("@EndRecord", "{1}");
            UtilityDAO dao = new UtilityDAO();            
            DataSet ds = dao.GetPagedData(SQL.ToString(), null, pageNo, pageSize, out totalRecord);

            return ds.Tables[0].Rows.Count > 0 ? ds : null;
        }

        #region Dynamic SQL Generation

        private void GenerateSQL(SavedSearch entity, StringBuilder SQL)
        {
            SQL.Append(_StaticSQL);
            SQL.Replace("[*USER_SELECTED_CONDITIONS*]", GenerateWhereClause(entity));
        }

        private String GenerateWhereClause(SavedSearch entity)
        {
            StringBuilder SQL = new StringBuilder();
            ///Add filters for Keyword, at first
            if (!entity.Keyword.IsNullOrEmpty())
            {                
                ISearcher searcher = SearcherFactory.GetSearcher(typeof(Job));
                String jobIDs = searcher.Search(entity.Keyword);
                if (jobIDs.IsNullOrEmpty())
                {
                    ///No Result Returned from Lucene, So, no need to proceed for further filtering.
                    //SQL.Append(" AND J.ID = 0");
                    SQL.AppendFormat(" AND JI.JobID = 0 {0}", Environment.NewLine);
                    return SQL.ToString();
                }
                else
                    //SQL.AppendFormat(" AND J.ID IN ({0}) {1}", jobIDs, Environment.NewLine);
                    SQL.AppendFormat(" AND JI.JobID IN ({0}) {1}", jobIDs, Environment.NewLine);
            }

            ///Add filters for Location (If Selected)
            if (entity.CityID.HasValue)
                SQL.AppendFormat(" AND J.CityID = {0} {1}", entity.CityID, Environment.NewLine);
            else if(entity.StateID.HasValue)
                SQL.AppendFormat(@" AND J.CityID IN 
                                        (
                                            SELECT City.ID FROM City WHERE City.StateID = {0} 
                                        ) {1}", entity.StateID, Environment.NewLine);
            else if(entity.CountryID.HasValue)
                SQL.AppendFormat(@" AND J.CityID IN 
                                        (
                                            SELECT City.ID FROM City 
                                            INNER JOIN State ON State.ID = City.StateID 
                                            INNER JOIN Country ON Country.ID = State.CountryID 
                                            WHERE Country.ID = {0} 
                                        ) {1}", entity.CountryID, Environment.NewLine);

            ///Salary Range filters
            if (entity.SalaryRange.HasValue && entity.SalaryRange > 0)
                SQL.AppendFormat(" AND J.SalaryRangeFrom >= {0} {1}", entity.SalaryRange, Environment.NewLine);
            ///Employee Type filters
            if (entity.EmployeeTypeID.HasValue && entity.EmployeeTypeID > 0)
                SQL.AppendFormat(" AND J.EmployeeTypeID = {0} {1}", entity.EmployeeTypeID, Environment.NewLine);
            ///Job Level filters
            if (entity.JobLevelID.HasValue && entity.EmployeeTypeID > 0)
                SQL.AppendFormat(" AND J.JobLevelID = {0} {1}", entity.JobLevelID, Environment.NewLine);
            ///Job Category filters
            if (entity.CategoryID.HasValue && entity.CategoryID > 0)
                SQL.AppendFormat(" AND J.CategoryID IN ({0}) {1}", _CategoryIDs, Environment.NewLine);
            ///Post Date filters
            if (entity.PostDate.HasValue && entity.PostDate > -1000)
                SQL.AppendFormat(" AND J.PostDate > DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), {0}) {1}", entity.PostDate, Environment.NewLine);           


            return SQL.ToString();
        }

        #endregion Dynamic SQL Generation

        #region SQL

        /// <summary>
        /// The BASE SQL to Dynamically filter jobs according to the user selection
        /// TODO: Needs to rewrite this SQL if the Database is MySQL, currently it works only in SQL Server
        /// </summary>
        private static String _StaticSQL = @"
            --DECLARE @StartRecord AS INT;
            --DECLARE @EndRecord AS INT;
            WITH SearchedJobs AS
	        (
		        SELECT
			        J.ID
			        , J.Title
			        , J.[Description]
			        , J.PostDate
			        , J.EndDate
			        , J.Url
			        , J.CompanyID
			        , J.CategoryID
			        , C.Name AS CompanyName
			        , C.Url AS CompanyUrl			
			        , C.LogoFile AS CompanyLogo
			        --Most Recent Jobs comes at first
			        , ROW_NUMBER() OVER (ORDER BY JI.JobID DESC) AS [RowNumber]
		        FROM JobIndex JI
			        INNER JOIN Job J ON J.ID = JI.JobID
			        INNER JOIN Company C ON C.ID = J.CompanyID			 
		        WHERE 
                    -- JI.JobID IN ([*LUCENE_JOB_IDS*])
		            -- Following checks are not needed but still its good to have them here.
		            -- Because inactive and deleted jobs are removed from Lucene Index
		            -- So, there is no way to return inactive job from Lucene Search
			        C.IsActive = 1
			        AND J.IsActive = 1			
			        AND J.IsDeleted = 0
			        AND J.EndDate >= GetDate()
			        [*USER_SELECTED_CONDITIONS*]
	        )
	        SELECT *		
		        , (SELECT MAX(RowNumber) FROM SearchedJobs) AS TotalRecord
	        FROM SearchedJobs
	        WHERE [RowNumber] BETWEEN @StartRecord AND @EndRecord";
        
        #endregion SQL
    }
}
